/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package aptech.lab.dao;

import aptech.lab.dao.model.Department;
import java.sql.Connection;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
/**
 *
 * @author vulh
 */
public class DbUtilMysql {
    Connection conn = null;
    String jdbcURL = "jdbc:mysql://192.168.72.11/learning";
    String jdbcDriver = "com.mysql.jdbc.Driver";
    String user = "learning";
    String password = "112358";
    /**
     *  String jdbcURL_oci = "jdbc:oracle:oci:@dqcsp";
     *	String jdbcURL_thin = "jdbc:oracle:thin:@192.168.101.104:1521:dqcsp";
     *  String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
     */
    public DbUtilMysql() {
        try {
            DbUtils.loadDriver(jdbcDriver);
            conn = DriverManager.getConnection(jdbcURL, user, password);
        } catch(SQLException e) {
            e.printStackTrace();
        }
    }
    
    private boolean reConnect() {
        boolean ret = true;
        try {
            DbUtils.loadDriver(jdbcDriver);
            conn = DriverManager.getConnection(jdbcURL, user, password);
        } catch(SQLException e) {
            e.printStackTrace();
            ret = false;
        }
        return ret;
    }
    
    public List<Department> getDepartments(int start, int rows) {
        QueryRunner qRunner = new QueryRunner();
        List<Department> results = new ArrayList<Department>();
        try {
            List mapList = (List) qRunner.query(conn, "select department_id, name from department limit ?,?", new MapListHandler(), start, rows);
            for (int i = 0; i < mapList.size(); i++) {
                Map map = (Map) mapList.get(i);
                Department department = new Department();
                department.setDepartmentId((Long)map.get("department_id"));
                department.setName((String)map.get("name"));
                results.add(department);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return results;
    }
    public Department getDepartment(long departmentId) {
        QueryRunner qRunner = new QueryRunner();
        Department department = new Department();
        try {
            List mapList = (List) qRunner.query(conn, "select department_id, name from department where department_id = ?", new MapListHandler(), departmentId);
            if (mapList.size()>0) {
                Map map = (Map) mapList.get(0);
                Long departId = (Long)map.get("department_id");
                String name = (String)map.get("name");
                department.setDepartmentId(departId);
                department.setName(name);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return department;
    }
    public int countDepartments() {
    	PreparedStatement statement = null;
        ResultSet resultSet = null;
        int results = 0;
        try {
        	String sql = "select count(*) from department";
        	statement = conn.prepareStatement(sql);
        	resultSet = statement.executeQuery();
        	if (resultSet.next()) {                
            	results = resultSet.getInt(1);
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
        	try {
	        	statement.close();
	        	resultSet.close();
        	} catch(Exception e) {}
        }
        return results;
    }
    
    public int addDepartment(Department department) {
        int inserts = -1;
        QueryRunner qRunner = new QueryRunner();
        try {
            inserts = qRunner.update(conn, "insert into department(name) values(?) ", department.getName());            
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return inserts;
    }
    
    public int updateDepartment(Department department) {
        int inserts = -1;
        QueryRunner qRunner = new QueryRunner();
        try {
            inserts = qRunner.update(conn, "update department set name = ? where department_id = ? ", department.getName(), department.getDepartmentId());
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return inserts;
    }
    
    public int deleteDepartment(long departmentId) {
        int inserts = -1;
        QueryRunner qRunner = new QueryRunner();
        try {
            inserts = qRunner.update(conn, "delete from department where department_id = ? ", departmentId);
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return inserts;
    }
    
    public List selectList(String sql) throws SQLException {
        ArrayList result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayListHandler();
        result = (ArrayList) run.query(conn, sql, h);
        return result;
    }
    
    public Object[] select(String sql, Object[] params) throws SQLException {
        Object[] result = null;
        QueryRunner run = new QueryRunner();
        ResultSetHandler h = new ArrayHandler();
        result = (Object[]) run.query(conn, sql, params, h);
        return result;
    }
    
    public static void main(String[] args) {
        DbUtilMysql db = new DbUtilMysql(); 
        Department depart = db.getDepartment(6l);
        System.out.println(depart.getName());
//        Department depart = new Department();
//        depart.setName("Software Devlopment 1");
//        db.addDepartment(depart);
//        depart.setName("Software Devlopment 2");
//        db.addDepartment(depart);
//        depart.setName("Software Devlopment 3");
//        db.addDepartment(depart);
//        depart.setName("Software Devlopment 4");
//        db.addDepartment(depart);
//        depart.setName("Software Devlopment 5");
//        db.addDepartment(depart);
//        depart.setName("Software Devlopment 6");
//        db.addDepartment(depart);
//        depart.setDepartmentId(2l);
//        int updates = db.updateDepartment(depart);
//        System.out.println("updates: " + updates);
//        List<Department> results = db.getDepartments(1, 10);
//        for (Department department : results) {
//            System.out.println("DepartmentId = " + department.getDepartmentId());
//            System.out.println("Name = " + department.getName());
//            System.out.println("-----------------");
//        }
    }
}
